package cn.edu.buaa.cnsatm.traffic_big_data_server.waterway.dao;

import cn.edu.buaa.cnsatm.traffic_big_data_server.util.JDBCUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class OceanDAO {
    public static ResultSet getOceanByLonAndLa(Double longitude, Double latitude){
        /**
         * @author: LX
         * @methodName: getOceanByLonAndLa
         * @description: 获取经纬度所在海域
         * @data: 2019-11-04
         **/
        String sql = "SELECT name FROM ocean WHERE ST_Contains(st_geomfromtext(st_astext(geom)),st_makepoint(" + longitude + "," + latitude + "))";
        //System.out.println(sql);
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = JDBCUtil.databaseConnecttion.createStatement();//创建数据库查询实体
            resultSet= stmt.executeQuery(sql);//执行Sql查询语句
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }
    public static ResultSet getOceanByGeom(String geom){
        /**
         * @author: LX
         * @methodName: getOceanByGeom
         * @description: 获线或者面相交情况判断所在海域
         * @data: 2019-11-15
         **/
        String sql = "SELECT name FROM ocean WHERE  ST_Intersects(geom, " + geom + ");";
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = JDBCUtil.databaseConnecttion.createStatement();//创建数据库查询实体
            resultSet= stmt.executeQuery(sql);//执行Sql查询语句
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }
    public static ResultSet getAllOceanCell(){
        /**
         * @author: LX
         * @methodName: getAllOceanCell
         * @description: 从cell表中获取所有海区格子信息
         * @data: 2019-12-07
         **/
        String sql = "SELECT * FROM cell";
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = JDBCUtil.databaseConnecttion.createStatement();//创建数据库查询实体
            resultSet= stmt.executeQuery(sql);//执行Sql查询语句
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }

    public static void updateCellAreaByCellID(String cellID, double cellArea){
        /**
         * @author: LX
         * @methodName: updateCellArea
         * @description: 将cell表中的每个cell面积求出
         * @data: 2019-12-07
         **/
        String sql = "UPDATE cell SET area = " + String.valueOf(cellArea)
                + " WHERE cell_id ='" + cellID + "'";
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = JDBCUtil.databaseConnecttion.createStatement();//创建数据库查询实体
            stmt.executeUpdate(sql);//执行Sql查询语句
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static void insertBoatInCell(String cellID, double boatDensity, String updateTime){
        /**
         * @author: LX
         * @methodName: insertBoatInCell
         * @description: 爬取海区格子中船的密度，存到数据库中
         * @data: 2019-12-07
         **/
        String sql = "INSERT INTO boat_in_cell (cell_id, boat_density, update_time) values('" +
                cellID + "', " + boatDensity + ", '" + updateTime + "')";
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = JDBCUtil.databaseConnecttion.createStatement();//创建数据库查询实体
            stmt.executeUpdate(sql);//执行Sql查询语句
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static ResultSet getBoatDensityByCellID(String cellID){
        /**
         * @author: LX
         * @methodName: getBoatDensityByCellID
         * @description: 根据cellID获取船密度
         * @data: 2019-12-07
         **/
        String sql = "SELECT boat_density FROM boat_in_cell WHERE cell_id = '" + cellID + "' AND update_time = (SELECT MAX(update_time) FROM boat_in_cell)";
        Statement stmt = null;
        ResultSet resultSet = null;
        try {
            stmt = JDBCUtil.databaseConnecttion.createStatement();//创建数据库查询实体
            resultSet= stmt.executeQuery(sql);//执行Sql查询语句
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return resultSet;
    }
}
